package com.ssy.lingxi.report.dao.settleaccount;

import com.ssy.lingxi.common.utils.DateUtil;
import com.ssy.lingxi.report.model.bo.MemberReceiptInvoiceBO;
import com.ssy.lingxi.report.model.bo.StatisticsCountBO;
import com.ssy.lingxi.report.model.constant.ReportStringEnum;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 会员票据报表DAO
 * @author 伍将
 * @date 2020/12/15
 * @version 2.0.0
 */
@Service
public class MemberReceiptInvoiceDAO {


    @Autowired
    @Qualifier("settleAccountJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 更新积分票据报表
     * @author 伍将
     * @date 2020/12/15
     * @return:
     **/
    public void updateScoreInvoiceReport() {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into sa_member_receipt_invoice ")
                .append(" (id,member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,order_time,order_amount,batch,pay_node,pay_amount,pay_time,invoice_status,pay_status,data_id,pay_member_id,pay_role_id,is_has_tax,tax_rate)")
                .append(" select ")
                .append(" nextval('sa_member_receipt_invoice_seq'), ")
                .append(" detail.member_id,detail.role_id,detail.order_no,detail.order_abstract,detail.settlement_order_type,")
                .append(" detail.order_type,detail.order_time,detail.settlement_amount, '1' batch,'"+ReportStringEnum.PAY_AFTER_SETTLEMENT.getMessage()+"' pay_node,detail.settlement_amount,")
                .append(" detail.pay_time,'0' invoice_status, CASE WHEN score.status>2 THEN 1 ELSE 0 end pay_status,data_id,0,0,0,0")
                .append(" from sa_platform_score_settlement score")
                .append(" inner join sa_platform_score_settlement_detail detail")
                .append(" on score.id=detail.settlement_id ")
                .append(" where score.status>1")
                .append(" ON CONFLICT (member_id,role_id,order_no,settlement_order_type,order_type,data_id,is_has_tax,tax_rate)")
                .append(" DO UPDATE SET pay_time=EXCLUDED.pay_time,pay_status=EXCLUDED.pay_status;");
        jdbcTemplate.execute(sql.toString());
    }

    /**
     * 更新物流票据报表
     * @author 伍将
     * @date 2020/12/15
     * @return:L
     **/
    public void updateLogisticsInvoiceReport() {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into sa_member_receipt_invoice ")
                .append(" (id,member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,order_time,order_amount,batch,pay_node,pay_amount,pay_time,invoice_status,pay_status,data_id,pay_member_id,pay_role_id,is_has_tax,tax_rate)")
                .append(" select ")
                .append(" nextval('sa_member_receipt_invoice_seq'), ")
                .append(" detail.member_id,detail.role_id,detail.order_no,detail.order_abstract,'2' settlement_order_type,")
                .append(" '999' order_type,detail.order_time,detail.order_amount, '1' batch,'"+ReportStringEnum.PAY_AFTER_SETTLEMENT.getMessage()+"' pay_node,detail.settlement_amount,")
                .append(" CASE WHEN sa.status>2 THEN sa.update_time ELSE 0 end pay_time,'0' invoice_status, CASE WHEN sa.status>2 THEN 1 ELSE 0 end pay_status,data_id,")
                .append(" detail.pay_member_id,detail.pay_role_id,is_has_tax,tax_rate")
                .append(" from sa_member_settlement sa")
                .append(" inner join sa_member_logistics_settlement detail")
                .append(" on sa.id=detail.settlement_id ")
                .append(" where sa.status>1")
                .append(" ON CONFLICT (member_id,role_id,order_no,settlement_order_type,order_type,data_id,is_has_tax,tax_rate)")
                .append(" DO UPDATE SET pay_time=EXCLUDED.pay_time,pay_status=EXCLUDED.pay_status;");
        jdbcTemplate.execute(sql.toString());
    }

    /**
     * 更新加工票据报表
     * @author 伍将
     * @date 2020/12/15
     * @return:L
     **/
    public void updateProductionNoticeInvoiceReport() {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into sa_member_receipt_invoice ")
                .append(" (id,member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,order_time,order_amount,batch,pay_node,pay_amount,pay_time,invoice_status,pay_status,data_id,pay_member_id,pay_role_id,is_has_tax,tax_rate)")
                .append(" select ")
                .append(" nextval('sa_member_receipt_invoice_seq'), ")
                .append(" detail.member_id,detail.role_id,detail.order_no,detail.order_abstract,'1' settlement_order_type,")
                .append(" '999' order_type,detail.order_time,detail.order_amount, '1' batch,'"+ReportStringEnum.PAY_AFTER_SETTLEMENT.getMessage()+"' pay_node,detail.settlement_amount,")
                .append(" CASE WHEN sa.status>2 THEN sa.update_time ELSE 0 end pay_time,'0' invoice_status, CASE WHEN sa.status>2 THEN 1 ELSE 0 end pay_status,data_id,")
                .append(" detail.pay_member_id,detail.pay_role_id ,is_has_tax,tax_rate")
                .append(" from sa_member_settlement sa")
                .append(" inner join sa_member_production_notice_settlement detail")
                .append(" on sa.id=detail.settlement_id ")
                .append(" where sa.status>1")
                .append(" ON CONFLICT (member_id,role_id,order_no,settlement_order_type,order_type,data_id,is_has_tax,tax_rate)")
                .append(" DO UPDATE SET pay_time=EXCLUDED.pay_time,pay_status=EXCLUDED.pay_status;");
        jdbcTemplate.execute(sql.toString());
    }

    /**
     * 更新请款单票据报表
     * @author ds
     * @date 2020/4/6
     * @return:
     **/
    public void updateApplyAmountOrderInvoiceReport() {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into sa_member_receipt_invoice ")
                .append(" (id,member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,order_time,order_amount,batch,pay_node,pay_amount,pay_time,invoice_status,pay_status,data_id,pay_member_id,pay_role_id,is_has_tax,tax_rate)")
                .append(" select ")
                .append(" nextval('sa_member_receipt_invoice_seq'), ")
                .append(" member_id,role_id,orderbo::jsonb#>>'{orderNo}','"+ReportStringEnum.INVOICE_SETTLEMENT.getMessage()+"','6' settlement_order_type,")
                .append(" '999' order_type,settlement_time,amount, '1' batch,'"+ReportStringEnum.PAY_AFTER_SETTLEMENT.getMessage()+"' pay_node,amount,")
                .append(" CASE WHEN sa.status>2 THEN sa.update_time ELSE 0 end pay_time,'0' invoice_status, CASE WHEN sa.status>2 THEN 1 ELSE 0 end pay_status,id data_id,")
                .append(" pay_member_id,pay_role_id,-1,0")
                .append(" from sa_member_settlement sa")
                .append(" where sa.status>1")
                .append(" ON CONFLICT (member_id,role_id,order_no,settlement_order_type,order_type,data_id,is_has_tax,tax_rate)")
                .append(" DO UPDATE SET pay_time=EXCLUDED.pay_time,pay_status=EXCLUDED.pay_status;");
        jdbcTemplate.execute(sql.toString());
    }

    /**
     * 更新订单票据报表
     * @author 伍将
     * @date 2020/12/16
     * @param list:
     * @return:
     **/
    public void updateInvoiceReport(List<MemberReceiptInvoiceBO> list) {
        if (list != null && list.size() > 0) {
            StringBuilder sql = new StringBuilder();
            sql.append(" insert into sa_member_receipt_invoice ")
                    .append(" (id,member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,order_time,order_amount,batch,pay_node,pay_amount,pay_time,invoice_status,pay_status,data_id,pay_member_id,pay_role_id,is_has_tax,tax_rate)")
                    .append(" values");
            list.forEach(o -> sql.append("(")
                    .append("nextval('sa_member_receipt_invoice_seq'), ")
                    .append(o.getMemberId() + "," + o.getRoleId() + ",'" + o.getOrderNo() + "','" + o.getOrderAbstract() + "'," + o.getSettlementOrderType() + ",")
                    .append(o.getOrderType() + "," + DateUtil.convertToTimeMillis(o.getOrderTime()) + "," + o.getOrderAmount() + "," + o.getBatch() + ",'" + o.getPayNode() + "'," + o.getPayAmount() + ",")
                    .append(DateUtil.convertToTimeMillis(o.getPayTime()) + "," + o.getInvoiceStatus() + "," + o.getPayStatus() + "," + o.getDataId() + ",")
                    .append(o.getPayMemberId() + "," + o.getPayRoleId() + ",")
                    .append(o.getIsHasTax() + "," + o.getTaxRate())
                    .append("),"));
            // 删除最后一个逗号
            sql.deleteCharAt(sql.length() - 1);
            sql.append(" ON CONFLICT (member_id,role_id,order_no,settlement_order_type,order_type,data_id,is_has_tax,tax_rate)")
                    .append(" DO UPDATE SET pay_time=EXCLUDED.pay_time,pay_status=EXCLUDED.pay_status;");
            jdbcTemplate.execute(sql.toString());
        }
    }

    /**
     * 获取待开票列表
     * @author 伍将
     * @date 2020/12/22
     * @return:
     **/
    public List<StatisticsCountBO> getToBeInvoiceList() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id,role_id,count(id) count")
                .append(" FROM ")
                .append(" sa_member_receipt_invoice ")
                .append(" where invoice_status=0")  // 待开票
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsCountBO.class));
    }
}
